/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.foxinline.servicos;

import br.com.foxinline.entidades.Bci;
import java.math.BigDecimal;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.Query;

/**
 *
 * @author roniere
 */
@Stateless
public class BciServico extends GenericService<Bci> {

    public BciServico() {
        super(Bci.class);
    }

//    public Bci find(Integer i){
//        return (Bci) getEntityManager().createQuery("select e from Bci e where e.id like '"+i+"'").getSingleResult();
//    }
    public List<Bci> findAll() {
        String sql = "select e from Bci e JOIN e.iptuAntigo i ORDER BY e.bairro ASC, i.valorBaseCorrigido ASC ";
        Query query = getEntityManager().createQuery(sql);
        return query.getResultList();
    }
    
    public List<Bci> existeUnidade(Bci i){
        String[] fator = i.getLocalizacaocartografica().split("\\.");
        String f = fator[0] +"."+ fator[1]+"."+ fator[2]+"."+ fator[3];
        String sql = "select e from Bci e where e.localizacaocartografica"
                + " like '"+f+"%' and e.tipoinsencao != 4 and e.tipoinsencao != 5 and e.tipoinsencao != 6 and e.tipoinsencao != 7"
                + " and e.tipoinsencao != 8  ";
        return (List<Bci>) getEntityManager().createQuery(sql).getResultList();
    }
    public BigDecimal areaconstruidaUnidades(Bci i){
        String[] fator = i.getLocalizacaocartografica().split("\\.");
        String f = fator[0] +"."+ fator[1]+"."+ fator[2]+"."+ fator[3];
        String sql = "select sum(e.area_da_unidade) from Bci e where e.localizacaocartografica"
                + " like '"+f+"%'";
        return (BigDecimal) getEntityManager().createQuery(sql).getSingleResult();
    }
    
    public BigDecimal sumNaoIsentos(BigDecimal isento){
        String sql = "select sum(e.valor) from Bci e where e.vvi > :vvi";
        
        Query query = getEntityManager().createQuery(sql);
        query.setParameter("vvi", isento);
        
        return (BigDecimal) query.getSingleResult();
    }
}
